package cnki.db.sql;

import cnki.entity.ArticleAbstractInfoEntity;
import cnki.entity.PeriodicalInfoEntity;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

import static com.han.http.util.Print.printlnErr;

/**
 * @author: Han
 * @date: 2018/9/3 13:22
 * @dscription: 数据库操作对象
 */
public class Operator {

    private static final Operator OPERATOR = new Operator();

    private Operator() {
    }

    public static Operator getInstance() {
        return OPERATOR;
    }

    public static void main(String... a) {
        Operator operator = getInstance();
        BufferedReader reader = null;
        try {
            reader = new BufferedReader(new InputStreamReader(new FileInputStream
                    ("C:\\Users\\Han\\Desktop\\abstract_info.sql")));
            String line;
            while ((line = reader.readLine()) != null) {
                operator.executeUpdate(line);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                Objects.requireNonNull(reader).close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 插入url信息
     *
     * @param entity 概要信息实体
     */
    public void insertAbstractInfo(ArticleAbstractInfoEntity entity) {
        var url = entity.getUrl();
        var periodicalId = entity.getPeriodicalId().strip();
        var title = entity.getTitle().strip();
        var author = entity.getAuthor().strip();
        var digest = entity.getDigest().strip();
        var fund = entity.getFund().strip();
        var keyword = entity.getKeyword().strip();
        var doi = entity.getDoi().strip();
        var typeNum = entity.getTypeNum().strip();
        var issue = entity.getIssue().strip();
        var cajUrl = entity.getCajUrl().strip();
        var pdfUrl = entity.getPdfUrl().strip();
        var sql = String.format("insert into article_abstract_info (url,periodical_id, title, author, abstract, fund, " +
                        "keyword, doi, type_num, issue, caj_download_url,pdf_download_url) values " +
                        "('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')", url, periodicalId,
                title, author, digest, fund, keyword, doi, typeNum, issue, cajUrl, pdfUrl);
        executeUpdate(sql);
    }

    public void updateAbstractInfo(ArticleAbstractInfoEntity entity) {
        var id = entity.getId();
        var title = entity.getTitle().strip();
        var author = entity.getAuthor().strip();
        var digest = entity.getDigest().strip();
        var fund = entity.getFund().strip();
        var keyword = entity.getKeyword().strip();
        var doi = entity.getDoi().strip();
        var typeNum = entity.getTypeNum().strip();
        var cajUrl = entity.getCajUrl().strip();
        var pdfUrl = entity.getPdfUrl().strip();
        var sql = String.format("update article_abstract_info set title = '%s', author = '%s', abstract = '%s'," +
                        " fund = '%s', keyword = '%s', doi = '%s', type_num = '%s',caj_download_url='%s'," +
                        "pdf_download_url='%s' where id = %d", title, author, digest, fund, keyword, doi, typeNum,
                cajUrl, pdfUrl, id);
        executeUpdate(sql);
    }

    /**
     * 执行更新语句
     *
     * @param sql 更新语句
     */
    private void executeUpdate(String sql) {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = DataSource.getConnection();
            statement = connection.createStatement();
            statement.executeUpdate(sql);
            connection.commit();
        } catch (SQLException e) {
            printlnErr(sql);
            e.printStackTrace();
        } finally {
            try {
                Objects.requireNonNull(statement).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                Objects.requireNonNull(connection).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 执行查询语句,返回结果集
     *
     * @param sql SQL语句
     * @return 操作者对象
     */
    private Handler executeQuery(String sql) {
        Handler handler = null;
        try {
            var connection = DataSource.getConnection();
            var statement = connection.createStatement();
            var resultSet = statement.executeQuery(sql);
            handler = new Handler(connection, statement, resultSet);
        } catch (SQLException e) {
            printlnErr(sql);
            e.printStackTrace();
        }
        return handler;
    }

    public List<ArticleAbstractInfoEntity> queryAbstractInfo(int start) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<ArticleAbstractInfoEntity> articleAbstractInfoEntityList = new ArrayList<>();
        try {
            connection = DataSource.getConnection();
            statement = connection.createStatement();
            resultSet =
                    statement.executeQuery(
                            "select * from article_abstract_info where title = '' limit " + start + ",100000");
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                var periodicalId = resultSet.getString("periodical_id");
                String url = resultSet.getString("url");
                String title = resultSet.getString("title");
                String author = resultSet.getString("author");
                String digest = resultSet.getString("abstract");
                String fund = resultSet.getString("fund");
                String keyword = resultSet.getString("keyword");
                String doi = resultSet.getString("doi");
                String typeNum = resultSet.getString("type_num");
                String issue = resultSet.getString("issue");
                var cajUrl = resultSet.getString("caj_download_url");
                var pdfUrl = resultSet.getString("pdf_download_url");
                ArticleAbstractInfoEntity entity = new ArticleAbstractInfoEntity(url, periodicalId, title, author, digest, fund, keyword, doi, typeNum, issue, cajUrl, pdfUrl);
                entity.setId(id);
                articleAbstractInfoEntityList.add(entity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                Objects.requireNonNull(resultSet).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                Objects.requireNonNull(statement).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                Objects.requireNonNull(connection).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return articleAbstractInfoEntityList;
    }

    /**
     * 插入期刊信息
     *
     * @param entity 期刊信息
     */
    public void insertPeriodicalInfoEntity(PeriodicalInfoEntity entity) {
        var name = entity.getName();
        var homePage = entity.getHomePage();
        var pubtype = entity.getPubtype();
        var pcode = entity.getPcode();
        var baseid = entity.getBaseid();
        var issue = entity.getIssue();
        var cif = entity.getCif();
        var aif = entity.getAif();
        var issn = entity.getIssn();
        var cn = entity.getCn();
        var sponsor = entity.getSponsor();
        var updateDate = entity.getUpdateDate();
        var createDate = entity.getCreateDate();
        var sql = "insert into periodical_info(name,home_page,pubtype,pcode,baseid,issue,cif,aif,issn,cn" +
                ",sponsor,update_date,create_date)value('" + name + "','" + homePage + "','" + pubtype + "'," +
                "'" + pcode + "','" + baseid + "','" + issue + "','" + cif + "','" + aif + "','" + issn + "','" +
                cn + "','" + sponsor + "','" + updateDate + "'," + "'" + createDate + "')";
        executeUpdate(sql);
    }

    /**
     * 更新期刊刊号
     *
     * @param entity 期刊实体
     */
    public void updatePeriodicalInfoIssue(PeriodicalInfoEntity entity) {
        var id = entity.getId();
        var issue = entity.getIssue();
        var sql = "update periodical_info set issue = '" + issue + "' where id = " + id;
        executeUpdate(sql);
    }

    /**
     * 查询期刊信息
     *
     * @return 期刊信息列表
     */
    public List<PeriodicalInfoEntity> queryPeriodicalInfo() {
        var list = new ArrayList<PeriodicalInfoEntity>();
        var sql = "select id,name,baseid,issue from periodical_info";
        var handler = executeQuery(sql);
        try {
            var resultSet = handler.getResultSet();
            while (resultSet.next()) {
                var id = resultSet.getInt("id");
                var name = resultSet.getString("name");
                var baseid = resultSet.getString("baseid");
                var issue = resultSet.getString("issue");
                var entity = new PeriodicalInfoEntity();
                entity.setId(id);
                entity.setName(name);
                entity.setBaseid(baseid);
                entity.setIssue(issue);
                list.add(entity);
            }
        } catch (NullPointerException | SQLException e) {
            e.printStackTrace();
        } finally {
            handler.closeHandler();
        }

        return list;
    }

    /**
     * 操作者对象
     */
    private class Handler {
        private Connection connection;
        private Statement statement;
        private ResultSet resultSet;

        private Handler(Connection connection, Statement statement, ResultSet resultSet) {
            this.connection = connection;
            this.statement = statement;
            this.resultSet = resultSet;
        }

        ResultSet getResultSet() {
            return resultSet;
        }

        /**
         * 关闭连接
         */
        private void closeConnection() {
            try {
                if (!statement.isClosed()) {
                    throw new SQLException("Statement is not closed");
                }
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        /**
         * 关闭语句对象
         */
        private void closeStatement() {
            try {
                if (!resultSet.isClosed()) {
                    throw new SQLException("ResultSet is not closed");
                }
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        /**
         * 关闭结果集
         */
        private void closeResultSet() {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        /**
         * 关闭所有对象
         */
        void closeHandler() {
            closeResultSet();
            closeStatement();
            closeConnection();
        }
    }
}
